'''
Knowtemarks
===========
A simple tool to manage your notes and bookmarks

@author: Markus Fuchs, 2012
'''

import logging
from datetime import datetime
import sqlite3 as sql

log = logging.getLogger("main.data")

class Tag():
    """
    Entity class to store in database which
    obviously represents a tag.
    Two tags are equal iff their string representations
    """
    tag_seperator = " "
    
    def __init__(self, name, id=None):
        self.name = name
        self.id = id
     
    def __repr__(self):
        return self.name
    
    def __eq__(self,other):
        return self.name == other.name
    def __ne__(self,other):
        return not self.__eq__(other)
    def __hash__(self):
        return self.name.__hash__()

class Knowte():
    """
    This class represents a note and a bookmark.
    It is considered as a note iff its "url" attribute
    is an empty string, otherwise it's a bookmark.
    
    Knowtes have a set of tags, which means there are no duplicate
    tags for a single item. Each knowte also has a title and an info
    field. In contrast to the info field, the title must not be empty.
    
    Two knowtes are equal iff they have the same title, the same info,
    the same url and the same tags.
    """
    def __init__(self, title="", info="", url="", id=123, tags=[], dateAdded=datetime.now(), dateModified=None):
        self.title = title
        self.info = info
        self.url = url
        self.setTags(tags)
        self.dateAdded = dateAdded
        self.dateModified = dateModified
    
    def getType(self):
        """
        @return: "bookmark" iff url is not "", otherwise "note"
        """
        if self.url is None or self.url == "":
            return "note"
        else:
            return "bookmark"
    
    def setTags(self, l):
        """
        @param l: a list of strings 
        """
        self.tags = [ Tag(x) for x in set(l) ]
    
    def addTag(self, t):
        """
        @param t: a string
        """ 
        self.tags.append(Tag(t))

    def taglist(self):
        """
        @return: a list of strings (the tags' names)
        """
        return [ t.name for t in self.tags ]
    
    def __repr__(self):
        return ">> %s: title: %s, info: %s, url: %s, tags: %s" % (self.getType(), self.title, self.info, self.url, self.tags)
    
    def __eq__(self,other):
        return self.tags == other.tags and self.title == other.title and self.info == other.info and self.url == other.url
    def __ne__(self,other):
        return not self.__eq__(other)
    def __hash__(self):
        return self.title.__hash__() ^ self.info.__hash__() ^ self.url.__hash__()
    
class KnowteDao(object):
    """
    The data access object for knowtes. Writes and retrieves knowtes
    to and from an sqlite database using sqlalchemy.
    """
    def __init__(self, dbfile):
        """ create a KnowteDao using the database in the file dbtfile where dbtfile is a string """
        self.conn = sql.connect(dbfile)
        self.conn.row_factory = sql.Row # get results as dict instead of tuple
        with self.conn:
            c = self.conn.cursor()
            c.execute("create table if not exists knowtes (id integer primary key, title text not null, info text, url text, dateAdded datetime not null, dateModified datetime)")
            c.execute("create table if not exists tags (id integer primary key, name text unique not null)")
            c.execute("create table if not exists has_tag (tag_id integer not null references tags (id), knowte_id integer not null references knowtes (id))")

    def storeKnowte(self, k):
        """
        Store a single knowte to the database including its tags.
        The transaction is commited such that data is consistently
        written after the method returns.
        The tags are retrieved from database and only added if they
        do not exists, such that tags are only stored once in the DB.
        This slows down a little, maybe another way can be found for this
        manual intervention with tags...
        """
        # TODO validate object
        with self.conn:
            k = self._addKnowte(self.conn.cursor(), k)
        return k
    
    def _addKnowte(self, cursor, k):
        # re-arrange tags to fullfill unique constraint
        cursor.execute("insert into knowtes (title, info, url, dateAdded) values (?,?,?,?)",
                                    ( k.title, k.info, k.url, datetime.now() ) )
        k.id = int(cursor.lastrowid)
        k = self._addTagsForKnowte(k, cursor)
        log.debug("Added knowte %s", k)
        return k
   
    def _addTagsForKnowte(self, k, cursor):
        newtags = []
        for t in k.tags:
            tdb_r = cursor.execute("select * from tags where name = ?", (t.name,)).fetchone()
            if tdb_r is not None:
                nt = Tag(tdb_r["name"])
                nt.id = tdb_r["id"]
                newtags.append(nt)
            else:
                cursor.execute("insert into tags (name) values (?)", (t.name,))
                id = int(cursor.execute("select id from tags where name = ?", (t.name,)).fetchone()[0])
                t.id = id
                newtags.append(t)
        for t in newtags: cursor.execute("insert into has_tag (knowte_id, tag_id) values (?,?)", (k.id, t.id))
        k.tags = newtags
        return k

    def storeKnowtes(self, kl):
        """
        Store a list of knowtes in the database. The transaction is 
        commited after the last knowte has been inserted.
        The tags are retrieved from database and only added if they
        do not exists, such that tags are only stored once in the DB.
        
        @return: id of the knowte in database
        """
        cursor = self.conn.cursor()
        with self.conn:
            for k in kl:
               k = self._addKnowte(cursor, k)
        
        return kl # TESTME: works?
    
    def _mapKnowte(self, row):
        """ maps result of a row from knowtes table to a Knowte object
            and fetches its corresponding tags 
        """
        if row is None: return None
        k = Knowte()
        k.id = row["id"]
        k.title = row["title"]
        k.info = row["info"]
        k.dateAdded = row["dateAdded"]
        k.dateModified = row["dateModified"]
        k.url = row["url"]
        # fetch tags
        with self.conn:
            tags = []
            for tr in self.conn.cursor().execute("select tags.name, tags.id from tags, has_tag where has_tag.knowte_id = ? and has_tag.tag_id = tags.id", (k.id,)).fetchall():
                t = Tag(tr["name"])
                t.id = tr["id"]
                tags.append(t)
            k.tags = tags
        return k
    
    def _mapKnowtes(self, rows):
        """ self._mapKnowte() for multiple results
            @return: a list of knowtes
        """
        if rows is None: return None
        return [ self._mapKnowte(r) for r in rows ]
                
    def _getall(self):
        res = None
        with self.conn:
            res = self._mapKnowtes(self.conn.cursor().execute("select * from knowtes").fetchall())
        return res    
    
    def getKnowteById(self, i):
        """ @param i: the id of the knowte to retrieve as int """ 
        res = None
        with self.conn:
            res = self._mapKnowte(self.conn.cursor().execute("select * from knowtes where id = ?", (i,)).fetchone())
        return res
    
    def getKnowtes(self, limit=0, offset=0, type="all"):
        """
        get knowtes in order of the date they were added, newest first
        @param limit: max number of knowtes to retrieve
        @param offset: the offset when to start retrieving
        @param type: either "all" for notes and bookmarks, "bookmark"
                     or "note" for bookmarks/notes only.
        """
        statement = "select * from knowtes"
        if type == "bookmark":
            statement += " where url != ''"
        elif type == "note":
            statement += " where url = ''"
            
        statement = statement + " order by dateAdded DESC"
        
        if limit > 0:
            statement += " limit %d" % limit
        if offset > 0:
            statement += " offset %d" % offset
        res = []
        with self.conn:
            res = self._mapKnowtes(self.conn.cursor().execute(statement).fetchall())
        return res
        
    
    def deleteKnowte(self, i):
        """ @param i: the id of the knowte to be deleted as int """
        with self.conn:
            self.conn.cursor().execute("delete from knowtes where id = ?", (i,))
    
    def editKnowte(self, i, k):
        """
        @param i: the id of the knowte as int
        @param k: the knowte object with the actual values 
        @return: the modified knowte on success, None otherwise 
        """
        with self.conn:
            c = self.conn.cursor()
            # first update knowte itself
            c.execute("update knowtes set title = ?, info = ?, url = ?, dateModified = ? where id = ?", (k.title, k.info, k.url, datetime.now(), i))
            # update tags: 1. delete all, 2. set new
            c.execute("delete from has_tag where knowte_id = ?", (i,))
            k = self._addTagsForKnowte(k, c)
            self.conn.commit()
            return k
        return None
        
    def getBookmarkCount(self):
        """ get a count of all knowtes whose type is "bookmark" """
        return self.conn.cursor().execute( "SELECT COUNT(*) FROM knowtes where url != ''" ).fetchall()[0][0]
    
    def getNoteCount(self):
        """ get a count of all knowtes whose type is "note" """
        return self.conn.cursor().execute( "SELECT COUNT(*) FROM knowtes where url = ''" ).fetchall()[0][0]
    
    def getKnowteCount(self):
        """ get count of all knowtes """
        return self.conn.cursor().execute( "SELECT COUNT(*) FROM knowtes" ).fetchall()[0][0]
